跳到主要内容

编写高级 SELECT 语句

本章中增大了使用 SELECT 语句可执行的操作的范围。并使您能够执行更复杂的数据库查询和数据处理。编写 SELECT 语句着重于 SELECT 语句语法中的五个子句。本章添加了 GROUP BY 子句和 HAVING 子句。可以将 GROUP BY 子句与聚集函数配合使用来组织 FROM 子句返回的行。可以包括 HAVING 子句来对 GROUP BY 子句返回的值设置条件。

本章还扩展了连接的早期讨论。它说明了自连接（它使您能够将表连接至它本身）和四种类型的外连接（在其中应用关键字 OUTER 来以不同的方式处理两个或多个连接的表）。本章还介绍了相关和非相关子查询及其操作关键字，显示了如何使用 UNION 运算符来组合查询。并定义了称为联合、相交和差异的集合运算。

本章中的示例显示如何在查询中使用 SELECT 语句子句的一部分或全部。子句必须按以下顺序显示

  1. Projection
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY
  7. INTO TEMP

有关以正确顺序使用所有这些子句的 SELECT 语句的示例，请参阅图 5。

附加 SELECT 语句子句 INTO （可用于在 SQL API 中指定程序和主变量）在 SQL 编程和随产品提供的出版物中进行描述。

本章还描述嵌套的 SELECT 语句，其中子查询在主查询的 Projection 、FROM 或 WHERE 子句中指定。其它几节说明 SELECT 语句如何定义和操作集合，以及如何对查询结果进行集合运算。

GROUP BY 和 HAVING 子句​

可选 GROUP BY 和 HAVING 子句向 SELECT 语句添加功能。可以在基本 SELECT 语句中包括一个或全部两个子句来增大处理聚集的能力。

GROUP BY 子句组合类似的行，针对 Projection 子句中列出的每个列，为具有相同值的每组行生成单一结果行。HAVING 子句在构成组之后对那些组设置条件。可以不带 HAVING 子句使用 GROUP BY 子句或不带 GROUP BY 子句使用 HAVING 子句。

GROUP BY 子句​

GROUP BY 子句将表分为几组。此子句通常与为每个这样的组生成总结值的聚集函数组合。编写 SELECT 语句中的某些示例显示了应用于整个表的聚集函数的用法。本章说明应用于行组的聚集函数。

使用不带聚集的 GROUP BY 子句与在 SELECT 子句中使用 DISTINCT（或 UNIQUE）关键字很相似。下列查询在选择特定列中描述。

图: 查询

SELECT DISTINCT customer_num FROM orders;

还可以按以下查询编写此语句。

图: 查询

SELECT customer_num FROM orders
GROUP BY customer_num;

图 1和图 2返回下列行。

图: 查询结果

customer_num

101
104
106
110
⋮
124
126
127

GROUP BY 子句将行收集到组中，因此每一组中的每一行具有相同的客户号。在没有选择任何其它列的情况下，结束是唯一 customer_num 值的列表。

GROUP BY 子句的功能在将它与聚集函数配合使用时更明显。

下列查询检索每个订单的商品数和所有商品的总价。

图: 查询

SELECT order_num, COUNT (*) number, SUM (total_price) price
FROM items
GROUP BY order_num;

GROUP BY 子句导致 items 表的行数被收集为组，每个组由具有相同 order_num 值的行组成（即，将每个订单的商品收集在一起）。在数据库服务器构成组之后，就在每个组中应用聚集行数 COUNT 和 SUM 。

图 4对每一组返回每一行。它还使用标号来为 COUNT 和 SUM 表达式的结果提供名称，如下所示。

图: 查询结果

order_num      number         price

1001           1          $250.00
1002           2         $1200.00
1003           3          $959.00
1004           4         $1416.00
⋮
1021           4         $1614.00
1022           3          $232.00
1023           6          $824.00

该结果将 items 表的行收集到具有相同订单号的组中，并计算每个组中行的 COUNT 和价格的 SUM。

不能在 GROUP BY 子句中包含 TEXT 、BYTE 、CLOB 或 BLOB 列。要进行分组，必须能够进行排序，并且这些数据类型不存在自然排序顺序。

与 ORDER BY 子句不同，GROUP BY 子句不对数据进行排序。如果想要按特定顺序对数据进行排序，或在投影列表中的聚集上排序，那么在 GROUP BY 子句之后包含 ORDER BY 子句。

下列查询与图 4相同，但包括 ORDER BY 子句以按 price 的升序对检索到的行进行排序，如下所示。

图: 查询

SELECT order_num, COUNT(*) number, SUM (total_price) price
FROM items
GROUP BY order_num
ORDER BY price;

图: 查询结果

order_num      number         price

1010           2           $84.00
1011           1           $99.00
1013           4          $143.80
1022           3          $232.00
1001           1          $250.00
1020           2          $438.00
1006           5          $448.00
⋮
1002           2         $1200.00
1004           4         $1416.00
1014           2         $1440.00
1019           1         $1499.97
1021           4         $1614.00
1007           5         $1696.00

选择特定列一节描述如何在 ORDER BY 子句中使用整数来指示投影列表中列的位置。还可以在 GROUP BY 子句中使用整数来指示列名的位置或在 GROUP BY 列表中显示标号。

以下查询返回与图 6所示相同的行。

图: 查询

SELECT order_num, COUNT(*) number, SUM (total_price) price
FROM items
GROUP BY 1
ORDER BY 3;

构建查询时，Projection 子句的投影列表中的所有非聚集列还必须包含在 GROUP BY 子句中。具有 GROUP BY 子句的 SELECT 语句必须针对每一组返回一行。列出在 GROUP BY 后面的列能够在一组中只反映一个特异值。并且可以返回该值。但是，未列出在 GROUP BY 后面的列可在包含在组中的行中包含不同的值。

下列查询显示如何在连接表的 SELECT 语句中使用 GROUP BY 子句。

图: 查询

SELECT o.order_num, SUM (i.total_price)
FROM orders o, items i
WHERE o.order_date > '01/01/98'
AND o.customer_num = 110
AND o.order_num = i.order_num
GROUP BY o.order_num;

该查询连接 orders 和 items 表，将表别名指定给它们，并返回以下所示的行。

图: 查询结果

order_num (sum)

1008 $940.00
1015 $450.00

HAVING 子句​

要完成 GROUP BY 子句，使用 HAVING 子句来在构成组之后将一个或多个限制条件应用于这些组。HAVING 子句对组的影响类似于 WHERE 子句限定个别行的方式，使用 HAVING 子句的一个优点是可以在搜索条件中包括聚集，而在 WHERE 子句的搜索条件中去不能包含聚集。

每个 HAVING 条件将组的一列或一个聚集表达式与组的另一个聚集表达式或与常量作比较。可以使用 HAVING 来对列值或组列表中的聚集值设置条件。

下列查询返回具有两个商品以上的订单上每个商品的平均总价格。HAVING 子句在每个组构成时测试每个组，并选择由两行以上构成的那些组。

图: 查询

SELECT order_num, COUNT(*) number, AVG (total_price) average
FROM items
GROUP BY order_num
HAVING COUNT(*) > 2;

图: 查询结果

order_num      number          average

1003           3          $319.67
1004           4          $354.00
1005           4          $140.50
1006           5           $89.60
1007           5          $339.20
1013           4           $35.95
1016           4          $163.50
1017           3          $194.67
1018           5          $226.20
1021           4          $403.50
1022           3           $77.33
1023           6          $137.33

如果使用不带 GROUP BY 子句的 HAVING 子句，那么 HAVING 条件应用于满足搜索条件的所有行。也就是说，满足搜索条件的所有行组成了一个组。

下列查询（图 1的修改版本）只返回一行，即表中所有 total_price 值的平均数，如下所示。

图: 查询

SELECT AVG (total_price) average
FROM items
HAVING count(*) > 2;

图: 查询结果

average

$270.97

如果图 3与图 1一样，在 Projection 子句中包含了非聚集列 order_num ，那么必须将 GROUP BY 子句与组列表中的列包含子啊一起。此外，如果不满足 HAVING 子句中的条件，那么输出将显示列标题以及一条消息指示没有找到任何行。

下列查询包含可以在 GBase 8s 版本的交互 SQL 中使用的所有 SELECT 语句子句（命名主变量的 INTO 子句只在 SQL API 中可用）。

图: 查询

SELECT o.order_num, SUM (i.total_price) price,
paid_date - order_date span
FROM orders o, items i
WHERE o.order_date > '01/01/98'
AND o.customer_num > 110
AND o.order_num = i.order_num
GROUP BY 1, 3
HAVING COUNT (*) < 5
ORDER BY 3
INTO TEMP temptab1;

该查询连接 orders 和 items 表；使用显示标号、表列名和用作列指示符的整数；对数据进行分组和排序；并将结果放置在临时表中，如下所示。

图: 查询结果

order_num            price        span

1017          $584.00
1016          $654.00
1012         $1040.00
1019         $1499.97          26
1005          $562.00          28
1021         $1614.00          30
1022          $232.00          40
1010           $84.00          66
1009          $450.00          68
1020          $438.00          71

创建高级连接​

创建连接一节显示如何在 SELECT 语句中包括 WHERE 子句以在一个列或多个列上连接两个或多个表。它说明了自然连接和等值连接。

本章讨论如何使用两种更复杂的连接：自连接和外连接。如对简单连接描述的那样，可以为表定义别名并将显示标号指定给表达式以缩短多表查询时间。还可以带 ORDER BY 子句发出 SELECT 语句，这将把数据排序到临时表中。

自连接​

连接不一定总是涉及两个不同的表，可以将表连接至它本身，创建自连接。当想要将列中的值与同一列中的其他值进行比较时，将表连接至它本身非常有用。

要创建自连接，在 FROM 子句中列出表两次，并且每次为它指定不同的别名。使用别名在 Projection 和 WHERE 子句中引用表。如同是两个独立的表一样。（SELECT 语句中的别名在别名和 GBase 8s SQL 指南：语法中讨论。）

与表之间的连接一样，可以在自连接中使用算术表达式，可以测试空值。可以使用 ORDER BY 子句来以升序或降序对指定列中的值进行排序。

下列查询查询 ship_weight 相差五倍或更多并且 ship_date 不为空的订单。接着，查询按照 ship_date 对数据进行排序。

图: 查询

SELECT x.order_num, x.ship_weight, x.ship_date,
y.order_num, y.ship_weight, y.ship_date
FROM orders x, orders y
WHERE x.ship_weight >= 5 * y.ship_weight
AND x.ship_date IS NOT NULL
AND y.ship_date IS NOT NULL
ORDER BY x.ship_date;

表 1. 查询结果

order_numship_weightship_dateorder_numship_weightship_date
100495.8005/30/1998101110.4007/03/1998
100495.8005/30/1998102014.0007/16/1998
100495.8005/30/1998102215.0007/30/1998
1007125.9006/05/1998101520.6007/16/1998
1007125.9006/05/1998102014.0007/16/1998

如果想要将自连接的结果存储到临时表中，那么将 INTO TEMP 子句追加到 SELECT 语句中，并至少对一组列指定显示标号，以重命名这些列。否则，重复列名将导致错误，并且不会创建临时表。

下列查询，类似于图 1，标记从 orders 表选择的所有列，并将这些列放置在称为 shipping 的临时表中。

图: 查询

SELECT x.order_num orders1, x.po_num purch1,
x.ship_date ship1, y.order_num orders2,
y.po_num purch2, y.ship_date ship2
FROM orders x, orders y
WHERE x.ship_weight >= 5 * y.ship_weight
AND x.ship_date IS NOT NULL
AND y.ship_date IS NOT NULL
ORDER BY orders1, orders2
INTO TEMP shipping;

如果您从表 shipping 中查询 SELECT *，可以看到下列行。

图: 查询结果

orders1 purch1     ship1          orders2 purch2    ship2

1004 8006       05/30/1998        1011 B77897    07/03/1998
1004 8006       05/30/1998        1020 W2286     07/16/1998
1004 8006       05/30/1998        1022 W9925     07/30/1998
1005 2865       06/09/1998        1011 B77897    07/03/1998
⋮
1019 Z55709     07/16/1998        1020 W2286     07/16/1998
1019 Z55709     07/16/1998        1022 W9925     07/30/1998
1023 KF2961     07/30/1998        1011 B77897    07/03/1998

可以多次将表连接至它本身。自连接的最大次数取决于您可用的资源。

下列查询中的自连接在 stock 表中创建由三个制造商供货的那些商品的列表。自连接在 WHERE 子句中包括最后两个条件，来除去行中检索到的重复的制造商代码。

图: 查询

SELECT s1.manu_code, s2.manu_code, s3.manu_code,
s1.stock_num, s1.description
FROM stock s1, stock s2, stock s3
WHERE s1.stock_num = s2.stock_num
AND s2.stock_num = s3.stock_num
AND s1.manu_code < s2.manu_code
AND s2.manu_code < s3.manu_code
ORDER BY stock_num;

图: 查询结果

manu_code manu_code manu_code stock_num description

HRO       HSK       SMT               1 baseball gloves
ANZ       NRG       SMT               5 tennis racquet
ANZ       HRO       HSK             110 helmet
ANZ       HRO       PRC             110 helmet
ANZ       HRO       SHM             110 helmet
ANZ       HSK       PRC             110 helmet
ANZ       HSK       SHM             110 helmet
ANZ       PRC       SHM             110 helmet
HRO       HSK       PRC             110 helmet
HRO       HSK       SHM             110 helmet
HRO       PRC       SHM             110 helmet
⋮
KAR       NKL       PRC             301 running shoes
KAR       NKL       SHM             301 running shoes
KAR       PRC       SHM             301 running shoes
NKL       PRC       SHM             301 running shoes

如果想要从 payroll 表选择行来确定哪些职员的薪水高于他们的经理，可以按以下 SELECT 语句所示构造自连接：

SELECT emp.employee_num, emp.gross_pay, emp.level,
emp.dept_num, mgr.employee_num, mgr.gross_pay,
mgr.dept_num, mgr.level
FROM payroll emp, payroll mgr
WHERE emp.gross_pay > mgr.gross_pay
AND emp.level < mgr.level
AND emp.dept_num = mgr.dept_num
ORDER BY 4;

下列查询使用相关子查询来检索并列出预订的 10 种价格最高的商品。

图: 查询

SELECT order_num, total_price
FROM items a
WHERE 10 >
(SELECT COUNT (*)
FROM items b
WHERE b.total_price < a.total_price)
ORDER BY total_price;

该查询返回 10 行。

图: 查询结果

order_num    total_price

1018      $15.00
1013      $19.80
1003      $20.00
1005      $36.00
1006      $36.00
1013      $36.00
1010      $36.00
1013      $40.00
1022      $40.00
1023      $40.00

可以创建类似的查询来查找并列出公司中资格最老的 10 个职员。

有关相关子查询的更多信息，请参阅SELECT 语句中的子查询。

外连接​

本章显示如何在 SELECT 语句中创建和使用外部连接。创建连接讨论内部连接。尽管内连接同等看待两个或多个连接的表，但外连接不同等看待两个或多个连接的表。外连接使其中一个表成为控制表（也称为外部表），控制其他从属表（也称为内部表）。

在内连接或简单连接中，结果只包含满足连接条件的行组合。废弃不满足连接条件的行。

在外连接中，结果包含满足连接条件的行与控制表中的行（如果在从属表中找不到匹配的行那么将废弃这些行）的组合。在从属表中无匹配行控制表的行在选自从属表的列中包括 NULL 值。

外连接允许您在应用连接条件之前将连接过滤器应用于内部表。

数据库服务器的较早版本只支持对用于外连接的 ANSI-SQL 标准语法的 GBase 8s 扩展。此语法仍受支持。然而，ANSI-SQL 标准语法在创建查询方面灵活性更高。建议使用 ANSI-SQL 标准语法来创建新查询。不管您使用何种形式的语法，必须将它用于单个查询块中的所有外连接。

在依赖于外连接之前，确定一个或多个内连接是否可工作。当不需要来自其它表的补充信息时，通常可以使用内连接。

限制

不能在同一查询块中组合 GBase 8s 和 ANSI 外连接语法。

有关外连接的语法的信息，请参阅《GBase 8s SQL 指南：语法》。

对外连接语法的 GBase 8s 扩展​

对外连接语法的 GBase 8s 扩展在外连接的开始处使用 OUTER 关键字。当使用 GBase 8s 语法时，必须在 WHERE 子句中包含连接条件。在将 GBase 8s 语法用于外连接时，数据库服务器支持以下三种基本类型的外连接：

  • 对两个表的外连接
  • 与第三个表进行简单连接的外连接
  • 将两个表与第三个表进行外连接

外连接必须具有 Projection 子句、FROM 子句和 WHERE 子句。连接条件在 WHERE 子句中表述。要将简单连接转换为外连接，在 FROM 子句中从属表的名称前面之间插入关键字 OUTER 。如在本节中后面所示，可以在查询中多次包括 OUTER 关键字。

没有对外连接语法的 GBase 8s 扩展等价于 ANSI 右外连接。

ANSI 连接语法​

以下 ANSI 连接受支持：

  • 左外连接
  • 右外连接

ANSI 外连接语法用 LEFT JOIN 、LEFT OUTER JOIN 、RIGHT JOIN 或 RIGHT OUTER JOIN 关键字开始外连接。OUTER 关键字是可选的。查询可在 ON 子句中指定连接条件和可选连接过滤器。WHERE 子句指定后连接（post-join）过滤器。另外，可以使用 LEFT 或 right 子句显式指定连接的类型。当用左括号开始连接时，ANSI 连接语法还允许外连接的控制部分或从属部分作为另一个连接的结果集。

如果将 ANSI 语法用于外连接，那么必须将 ANSI 语法用于单个查询块中的所有外连接。

提示

为了简介起见，本节中的示例使用表别名。别名 讨论表别名。

左外连接​

在左外连接的语法中，外连接的控制表显示在开始外连接的关键字左边。左外连接返回连接条件为 true 的所有行，除此之外，还返回控制表中的所有其它行并将从属表中的相应值显示为 NULL。

下列查询使用 ANSI 语法 LEFT OUTER JOIN 来获取与图 1（它使用 GBase 8s 外连接语法）相同的结果：

图: 查询

SELECT c.customer_num, c.lname, c.company, c.phone,
u.call_dtime, u.call_descr
FROM customer c LEFT OUTER JOIN cust_calls u
ON c.customer_num = u.customer_num;

在此示例中，可以使用 ON 子句来指定连接条件。可以在 WHERE 子句中添加其它过滤器来限制结果集；此类过滤器是后连接（post-join）过滤器。

以下查询只返回客户没有致电客户服务中心的行。在此查询中，数据库服务器对 customer 和 cust_calls 表的 customer_num 列执行外连接之后在 WHERE 子句中应用过滤器。

图: 查询

SELECT c.customer_num, c.lname, c.company, c.phone,
u.call_dtime, u.call_descr
FROM customer c LEFT OUTER JOIN cust_calls u
ON c.customer_num = u.customer_num
WHERE u.customer_num IS NULL;

除了前面的示例之外，下列示例显示了可与 ANSI 连接语法配合使用的各种查询构造类型：

SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
ON t1.c1=t3.c1) JOIN (t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
ON t1.c1=t4.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
ON t1.c1=t3.c1),
(t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
WHERE t1.c1 = t4.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
ON t1.c1=t3.c1) LEFT OUTER JOIN (t4 JOIN t5 ON t4.c1=t5.c1)
ON t1.c1=t4.c1;

SELECT *
FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
ON t1.c1=t2.c1;

SELECT *
FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
ON t1.c1=t3.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
LEFT OUTER JOIN t3 ON t2.c1=t3.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
LEFT OUTER JOIN t3 ON t1.c1=t3.c1;

SELECT *
FROM t9, (t1 LEFT JOIN t2 ON t1.c1=t2.c1),
(t3 LEFT JOIN t4 ON t3.c1=10), t10, t11,
(t12 LEFT JOIN t14 ON t12.c1=100);

SELECT * FROM
((SELECT c1,c2 FROM t3) AS vt3(v31,v32)
LEFT OUTER JOIN
( (SELECT c1,c2 FROM t1) AS vt1(vc1,vc2)
LEFT OUTER JOIN
(SELECT c1,c2 FROM t2) AS vt2(vc3,vc4)
ON vt1.vc1 = vt2.vc3)
ON vt3.v31 = vt2.vc3);

上面最后一个示例说明了关于派生表的连接。它指定将外查询的 FROM 子句中子查询的结果和另一个其它两个子查询结果的左外连接的结果进行左外连接。请参阅FROM 子句中的子查询获得较为简单的符合 ANSI 语法的子查询示例。

右外连接​

在右外连接的语法中，外连接的控制表显示在开始外连接的关键字右边。右外连接返回连接条件为 true 的所有行，除此之外，还返回控制表中的所有其它行并将从属表中的相应值显示为 NULL。

下列查询是对 customer 和 orders 表上的右外连接的一个示例。

图: 查询

SELECT c.customer_num, c.fname, c.lname, o.order_num,
o.order_date, o.customer_num
FROM customer c RIGHT OUTER JOIN orders o
ON (c.customer_num = o.customer_num);

该查询返回控制表 orders 中的所有行，并且在必要时，将从属表 customer 中的相应值显示为 NULL。

图: 查询结果

customer_num   fname    lname order_num   order_date  customer_num
104  Anthony  Wiggins      1001   05/30/1998           104
101  Ludwig     Pauli      1002   05/30/1998           101
104 Anthony   Wiggins      1003   05/30/1998           104
<NULL>  <NULL>    <NULL>      1004   06/05/1998           106

简单连接​

以下查询是 customer 和 cust_calls 表上简单连接的示例。

图: 查询

SELECT c.customer_num, c.lname, c.company,
c.phone, u.call_dtime, u.call_descr
FROM customer c, cust_calls u
WHERE c.customer_num = u.customer_num;

该查询只返回客户已致电客户服务中心的那些行，如下所示。

图: 查询结果

customer_num  106
lname         Watson
company       Watson & Son
phone         415-389-8789
call_dtime    1998-06-12 08:20
call_descr    Order was received, but two of the cans of
ANZ tennis balls within the case were empty
⋮
customer_num  116
lname         Parmelee
company       Olympic City
phone         415-534-8822
call_dtime    1997-12-21 11:24
call_descr    Second complaint from this customer! Received
two cases right-handed outfielder gloves (1 HRO)
instead of one case lefties.

对两个表的简单外连接​

下列查询使用与前面示例相同的 Projection 子句、表和比较条件，但这一次它用 GBase 8s 扩展语法创建简单外连接。

图: 查询

SELECT c.customer_num, c.lname, c.company,
c.phone, u.call_dtime, u.call_descr
FROM customer c, OUTER cust_calls u
WHERE c.customer_num = u.customer_num;

cust_calls 表前面的附加关键字 OUTER 使该表成为从属表。外连接导致查询返回有关所有客户的信，而不管它们是否已致电客户服务中心。检索控制表 customer 的所有行，并且将 NULL 值指定给从属表 cust_calls 的列，如下所示。

图: 查询结果

customer_num  101
lname         Pauli
company       All Sports Supplies
phone         408-789-8075
call_dtime
call_descr

customer_num  102
lname         Sadler
company       Sports Spot
phone         415-822-1289
call_dtime
call_descr
⋮
customer_num  107
lname         Ream
company       Athletic Supplies
phone         415-356-9876
call_dtime
call_descr

customer_num  108
lname         Quinn
company       Quinn's Sports
phone         415-544-8729
call_dtime
call_descr

与第三个表进行简单连接的外连接​

使用 GBase 8s 语法，下列查询显示作为第三个表的简单连接结果的外连接。这第二种类型的外连接也称为嵌套简单连接。

图: 查询

SELECT c.customer_num, c.lname, o.order_num,
i.stock_num, i.manu_code, i.quantity
FROM customer c, OUTER (orders o, items i)
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
AND manu_code IN ('KAR', 'SHM')
ORDER BY lname;

该查询首先对 orders 和 items 表执行简单连接，并检索 manu_code 为 KAR 或 SHM 的商品的所有订单的信息。然后，它执行外连接以将此消息与控制 customer 表的数据结合。可选的 ORDER BY 子句将数据重组为以下格式。

图: 查询结果

customer_num lname             order_num stock_num manu_code quantity

114 Albertson
118 Baxter
113 Beatty
⋮
105 Vector
121 Wallack                1018       302 KAR              3
106 Watson

将两个表与第三个表相连接​

使用 GBase 8s 扩展语法，下列查询显示作为两个表分别与第三个表的外连接结果的外连接。在此第三种类型的外连接中，连接关系可能仅仅是控制表与从属表之间的关系。

图: 查询

SELECT c.customer_num, c.lname, o.order_num,
order_date, call_dtime
FROM customer c, OUTER orders o, OUTER cust_calls x
WHERE c.customer_num = o.customer_num
AND c.customer_num = x.customer_num
ORDER BY lname
INTO TEMP service;

该查询分别将从属表 orders 和 cust_calls 连接至控制表 customer；它不连接两个从属表。INTO TEMP 子句将结果选择至临时表以供进一步处理或查询，如下所示。

图: 查询结果

customer_num lname             order_num order_date call_dtime

114 Albertson
118 Baxter
113 Beatty
103 Currie
115 Grant                  1010 06/17/1998
⋮
117 Sipes                  1012 06/18/1998
105 Vector
121 Wallack                1018 07/10/1998 1998-07-10 14:05
106 Watson                 1004 05/22/1998 1998-06-12 08:20
106 Watson                 1014 06/25/1998 1998-06-12 08:20

如果图 1尝试在两个从属表 o 和 x 之间创建连接条件（如下所示），一条错误消息将指示创建两侧外连接。

图: 查询

WHERE o.customer_num = x.customer_num

组合外连接的连接​

要实现多级嵌套，可以创建使用三种外连接类型的任何组合的连接。使用 ANSI 语法，以下查询创建了作为对两个表与另一个外连接的简单外连接组合结果的连接。

图: 查询

SELECT c.customer_num, c.lname, o.order_num,
stock_num, manu_code, quantity
FROM customer c, OUTER (orders o, OUTER items i)
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
AND manu_code IN ('KAR', 'SHM')
ORDER BY lname;

该查询首先执行 orders 和 items 表上的外连接，并检索有关 manu_code 为 KAR 或 SHM 的商品的所有订单的信息。然后，它执行组合此信息与控制表 customer 的数据的另一个外连接。

图: 查询结果

customer_num lname             order_num stock_num manu_code quantity

114 Albertson
118 Baxter
113 Beatty
103 Currie
115 Grant                  1010
⋮
117 Sipes                  1012
117 Sipes                  1007
105 Vector
121 Wallack                1018       302 KAR              3
106 Watson                 1014
106 Watson                 1004

当将外连接应用于某外连接与第三个表的结果时可用两种方法指定连接查询。两个从属表已连接，但如果控制表和从属表共享公共列，那么可以将控制表连接至任一从属表而不影响结果。

PIVOT 子句和UNPIVOT子句​

可选 PIVOT子句 和 UNPIVOT 子句向 SELECT 语句添加功能。可以在基本的SELECT语句中增加结果展示的灵活性。

PIVOT子句将指定列的列值作为查询结果集的列名，实现将行旋转为列的表格查询，并在旋转过程中聚合数据。UNPIVOT 子句将指定列的列名转换为查询结果集的列值,实现将列旋转为行。

PIVOT子句​

PIVOT子句用于查询中，实现将行旋转为列的表格查询。

原始数据准备：

create table demo(id int,name varchar(20),quarter int,nums int,amunt_sold decimal(8,2));
insert into demo values(1,'a',1,1000,200);
insert into demo values(2,'a',2,2000,100.12);
insert into demo values(3,'a',3,4000,156);
insert into demo values(4,'b',1,5000,234);
insert into demo values(5,'b',3,3000,345);
insert into demo values(6,'c',3,3500,222);
insert into demo values(7,'d',1,4200,111);
insert into demo values(8,'d',2,5500,555);

下列查询将name的列值a、b、c、d转换为列，并将这些列的nums分别做sum聚合操作，只返回一行，如下所示：

图:查询

select * from (select name,nums from demo)
PIVOT (sum(nums) for name
in('a','b','c','d'));

图:查询结果

‘a’       ‘b’      ‘c’       ‘d’
7000	 8000      3500	9700

下列查询将(id,name)的列值(1,a)、(4,b)、(6,c)、(8,d)分别转换为列，列名为以下划线(_)连接的两列的组合（即1_a、4_b、6_c、8_d），并将这些列的nums分别做sum聚合操作，只返回一行，如下所示：

图:查询

select * from (select id,name,nums from demo)
PIVOT (sum(nums) for (id,name)
in((1,'a'),(4,'b'),(6,'c'),(8,'d')));

图:查询结果

1_a 4_b 6_c 8_d

1000 5000 3500 5500

下列查询将name的列值a、b分别转换为列，并将这些列的nums、amunt_sold分别做sum聚合操作，得到笛卡尔积组合的四列，列名为以下划线(_)连接的列名组合，只返回一行，如下所示：

图:查询

select * from (select name,nums,amunt_sold from demo)
PIVOT (sum(nums) as nums,sum(amunt_sold) as amunt_sold
for name in('a','b'));

图:查询结果

a_nums b_nums a_amunt_sold b_amunt_sold

7000 8000 456.12 579.00

下列查询将name的列值(a、b、c、d)分别转换为列，并为a指定别名no，将投影列（quarter,name,nums）中除去参数（name、nums）以后，其余的列（quarter）也作为新表的列，然后按照其余列（quarter）分组，并将这些列的nums分别做sum聚合操作，返回多行，如下所示：

图:查询

select * from (select quarter,name,nums from demo)
PIVOT (sum(nums) for name
in('a' as no,'b','c','d'));

图:查询结果

quarter   no         b      c       d
1	1000	  5000	（NULL） 4200
2	2000	（NULL）（NULL） 5500
3	4000	  3000	  3500	（NULL）

下列查询将(id,name)的列值(1,a)、(4,b)、(6,c)、(8,d)分别转换为列，将投影列（id,quarter,name,nums,amunt_sold）中除去参数（id,name,nums,amunt_sold）以后，其余的列（quarter）也作为新表的列，然后按照其余列（quarter）分组，并将这些列的nums, amunt_sold分别做sum聚合操作，新列名为多列的组合，再与多个聚集函数的别名分别组合，以下划线（_）相连，聚集函数不指定别名时，默认以‘_1’,'_2'做区分。查询返回多行，如下所示：

图:查询

select * from demo
PIVOT (sum(nums) ,sum(amunt_sold) for (id,name)
in((1,'a'),(4,'b'),(6,'c'),(8,'d')));

图:查询结果

quarter  1_a    4_b     6_c     8_d    1_a_1    4_b_1    6_c_1    8_d_1
1     1000   5000   (NULL)  (NULL)  200.00   234.00   (NULL)   (NULL)
2    (NULL)  (NULL)  (NULL)  5500   (NULL)   (NULL)   (NULL)   555.00
3    (NULL)  (NULL)  3500   (NULL)  (NULL)   (NULL)   222.00   (NULL)

UNPIVOT子句​

UNPIVOT子句用于查询中，实现将列旋转为行的表格查询。

原始数据准备：

create table fruit(id int,name varchar(20),Q1 int,Q2 int,Q3 int,Q4 int);
insert into fruit values(1,'a',1000,2000,3300,5000);
insert into fruit values(2,'b',3000,3000,3200,1500);
insert into fruit values(3,'c',2500,3500,2200,2500);
insert into fruit values(4,'d',1500,2500,1200,3500);
insert into fruit values(5,'e',null,null,null,null);

下列查询将属性Q1,Q2,Q3,Q4转换为列值，并为该列取一个属性名quarter，而原表的列值则作为属性sold的列值，select语句投影列（id,name,Q1,Q2,Q3,Q4）除去参数（Q1,Q2,Q3,Q4）以后，其余列（id,name）也作为新表列。在转换过程中，将其余列每行数据分别与（Q1,Q2,Q3,Q4）一一对应并组合，其组合值作为新表的行值出现，查询不包括空值如下所示：

图:查询

select id,name,quarter,sold from fruit
unpivot (sold for quarter in(Q1,Q2,Q3,Q4));

图:查询结果

id	name	quarter	sold
1	a	q1	1000
1	a	q2	2000
1	a	q3	3300
1	a	q4	5000
2	b	q1	3000
2	b	q2	3000
2	b	q3	3200
2	b	q4	1500
3	c	q1	2500
3	c	q2	3500
3	c	q3	2200
3	c	q4	2500
4	d	q1	1500
4	d	q2	2500
4	d	q3	1200
4	d	q4	3500

下列查询比上述查询增加了include nulls以显示NULL值，如下所示

图:查询

select id,name,quarter,sold from fruit
unpivot include nulls (sold for quarter in(Q1,Q2,Q3,Q4));

图:查询结果

id	name	quarter	sold
1	a	q1	1000
1	a	q2	2000
1	a	q3	3300
1	a	q4	5000
2	b	q1	3000
2	b	q2	3000
2	b	q3	3200
2	b	q4	1500
3	c	q1	2500
3	c	q2	3500
3	c	q3	2200
3	c	q4	2500
4	d	q1	1500
4	d	q2	2500
4	d	q3	1200
4	d	q4	3500
5	e	q1
5	e	q2
5	e	q3
5	e	q4

SELECT 语句中的子查询​

子查询（内部 SELECT 语句，其中一个 SELECT 语句嵌套在另一个 SELECT 语句中）可以返回多行或多个表达式，也可以不返回任何结果。每个子查询必须用括号分隔，并且都必须包含一个 Projection 子句和一个 FROM 子句，子查询本身可以包含其它子查询。

数据库服务器支持下列上下文中的子查询：

  • 嵌套在另一个 SELECT 语句的 Projection 子句中的 SELECT 语句
  • 嵌套在另一个 SELECT 语句中的 WHERE 子句中的 SELECT 语句
  • 嵌套在另一个 SELECT 语句的 FROM 子句中的 SELECT 语句

还可以在 INSERT 、DELETE 、MERGE 或 UPDATE 语句（子查询有效）的各种子句中指定子查询。

Projection 子句或 WHERE 子句中的子查询可以是相关的或是不相关的。当子查询产生的值取决于包含它的外部 SELECT 语句产生的值时，该子查询是相关的。有关更多信息，请参阅相关子查询。

任何其它类型的子查询都被认为是不相关的。在 SELECT 语句的 FROM 子句中，只有不相关的子查询才是有效的。

相关子查询​

相关子查询是引用不列在其 FROM 子句中的表的列的子查询。该列可以在 Projection 子句或在 WHERE 子句中。要查找相关子查询引用的表，搜索列直到找到相关为止。

通常，相关子查询会降低性能。在子查询中使用表名或别名，这样就不会对所在的表产生疑问。

数据库服务器将使用外查询来获取值。例如：如果表 taba 具有列 col1，表 tabb 具有列 col2，并且它们包含以下内容：

taba.col1 aa,bb,null
tabb.col2 bb, null

那么查询为：

select * from taba where col1 in (select col1 from tabb);

那么结果可能会毫无意义。数据库服务器将提供 taba.col1 中所有的值，并接着它们与 taba.col1 进行比较（外查询 WHERE 子句）。这将返回所有的行。通常使用子查询从内表返回列值。如果查询写成：

select * from taba where col1 in (select tabb.col1 from tabb);

那么将导致错误 error -217 column not found。

相关子查询的重要功能是，由于它取决于来自外部 SELECT 的值。所以必须重复执行它，对外部 SELECT 产生的每个值执行一次。非相关子查询只能执行一次。

SELECT 语句中的子查询​

可以构造具有子查询的 SELECT 语句来替换两个独立的 SELECT 语句。

SELECT 语句中的子查询允许您执行各种任务，包括下列操作：

  • 将表达式与另一 SELECT 语句的结果进行比较
  • 确定另一 SELECT 语句的结果是否包含特定的表达式
  • 确定另一 SELECT 语句是否选择任何行

子查询中的可选 WHERE 子句通常用于缩小搜索条件。

子查询选择值并将值返回到第一个或外部 SELECT 语句。子查询可以不返回任何值。返回单个值或返回一组值，如下所示：

  • 如果子查询不返回任何值，那么查询不返回任何行。该子查询等价于 NULL 值。
  • 如果子查询返回一个值，那么该值的格式为一个聚集表达式或就是一行和一列。此类子查询等价于一个数字或字符值。
  • 如果子查询返回一列或一组值，那么这些值可表示一行或一列。
  • 在外部查询的 FROM 子句中，子查询可表示一组行（有时候称为派生表或表表达式）。

Projection 子句中的子查询​

子查询可发生在另一个 SELECT 语句的 Projection 子句中。下列查询显示如何在 Projection 子句中使用子查询来返回 customer 表中每个顾客的总装运费用（来自 orders 表）。还可以将此查询编写为两个表之间的连接。

图: 查询

SELECT customer.customer_num,
(SELECT SUM(ship_charge)
FROM orders
WHERE customer.customer_num = orders.customer_num)
AS total_ship_chg
FROM customer;

图: 查询结果

customer_num total_ship_chg

101         $15.30
102
103
104         $38.00
105
⋮
123          $8.50
124         $12.00
125
126         $13.00
127         $18.00
128

FROM 子句中的子查询​

本节描述作为嵌套在外部 SELECT 语句的 FROM 子句中发生的子查询。由于外部查询使用子查询的结果作为数据源，因而此类子查询有时候称为派生表或表表达式。

下列查询在外部查询中使用星号表示法来返回检索 employee 表中 address 列所有字段的子查询的结果。

图: 查询

SELECT * FROM (SELECT address.* FROM employee);

图: 查询结果

address   ROW(102 Ruby, Belmont, CA, 49932, 1000)
address   ROW(133 First, San Jose, CA, 85744, 4900)
address   ROW(152 Topaz, Willits, CA, 69445, 1000))
⋮

这说明了如何指定派生表，但是它只是该语法的一个价值不高的示例，因为外部查询不操作 FROM 子句中的子查询返回的表表达式中的任何值。（请参阅图 1获取返回相同结果的简单查询。）

下列查询是一个更复杂的示例，其中外部查询仅选择派生表中满足条件的第一行，FROM 子句中的子查询将此派生表中满足条件的第一行，FROM 子句中的子查询将此派生表指定为 customer 和 cust_calls 表的简单连接。

图: 查询

SELECT LIMIT 1 * FROM
(SELECT c.customer_num, c.lname, c.company,
c.phone, u.call_dtime, u.call_descr
FROM customer c, cust_calls u
WHERE c.customer_num = u.customer_num
ORDER BY u.call_dtime DESC);

该查询只返回客户已致电客户服务中心的那些行，如下所示。

图: 查询结果

customer_num  106
lname         Watson
company       Watson & Son
phone         415-389-8789
call_dtime    1998-06-12 08:20
call_descr    Order was received, but two of the cans of
ANZ tennis balls within the case were empty

在前面的示例中，子查询包括 ORDER BY 子句，它指定出现在子查询的 Projection 列表中的一列，但如果 Projection 列表省略了 u.call_dtime列，查询还是有效的。子查询仅可在 FROM 子句这个上下文中指定 ORDER BY 子句。

WHERE 子句中的子查询​

本节描述嵌套在另一 SELECT 语句的 WHERE 子句中的 SELECT 语句发生的子查询。

可以将任何关系运算符与 ALL 和 ANY 配合使用来将一些内容与子查询生成的值的每一个（ALL）或任一个（ANY）进行比较。可以使用关键字 SOME 代替 ANY。运算符 IN 等价于 = ANY。要创建相反的搜索条件，使用关键字 NOT 或另一个关系运算符。

EXISTS 运算符对子查询进行测试以了解子查询是否找到了任何值。即，该运算符询问子查询的结果是否非空。不能在包含具有 TEXT 或 BYTE 数据类型的列的子查询中使用 EXISTS 关键字。

有关用于创建带子查询的条件的语法，请参阅《GBase 8s SQL 指南：语法》。

下列关键字介绍了 SELECT 语句的 WHERE 子句中的子查询。

ALL 关键字​

在子查询前面使用 ALL 关键字来确定对返回的每个值的比较是否为 true。如果子查询不返回任何值，那么搜索条件为 true。（如果子查询不返回任何值，那么对于所有零值条件为 true 。）

下列查询列出了包含总价小于订单号 1023 中每个商品的总价的商品的所有订单的以下信息。

图: 查询

SELECT order_num, stock_num, manu_code, total_price
FROM items
WHERE total_price < ALL
(SELECT total_price FROM items
WHERE order_num = 1023);

图: 查询结果

order_num stock_num manu_code total_price

1003         9 ANZ            $20.00
1005         6 SMT            $36.00
1006         6 SMT            $36.00
1010         6 SMT            $36.00
1013         5 ANZ            $19.80
1013         6 SMT            $36.00
1018       302 KAR            $15.00

ANY 关键字​

在子查询前面使用关键字 ANY （或它的同义词 SOME）来确定是否对至少一个返回值的比较为 true 。如果子查询不返回任何值，那么搜索条件为false。（因为没有值存在，所以对于其中一个值条件不能为 true 。）

以下查询查找包含总价大于订单号 1005 中任何一个商品总价的商品的所有订单的订单号。

图: 查询

SELECT DISTINCT order_num
FROM items
WHERE total_price > ANY
(SELECT total_price
FROM items
WHERE order_num = 1005);

图: 查询结果

order_num

1001
1002
1003
1004
⋮
1020
1021
1022
1023

单值子查询​

如果您知道子查询可能对外部级别查询返回刚好一个值，那么不需要 ALL 或 ANY 。可如同对待函数一样对待只返回一个值的子查询。这种子查询通常使用聚集函数，原因是聚集函数总是返回单个的。

下列查询在子查询中使用聚集函数 MAX 查找包括最大排球网数目的订单的 order_num。

图: 查询

SELECT order_num FROM items
WHERE stock_num = 9
AND quantity =
(SELECT MAX (quantity)
FROM items
WHERE stock_num = 9);

图: 查询结果

order_num

1012

下列查询在子查询中使用聚集函数 MIN 选择总价高于最小价格 10 倍的商品。

图: 查询

SELECT order_num, stock_num, manu_code, total_price
FROM items x
WHERE total_price >
(SELECT 10 * MIN (total_price)
FROM items
WHERE order_num = x.order_num);

图: 查询结果

order_num stock_num manu_code  total_price

1003         8 ANZ           $840.00
1018       307 PRC           $500.00
1018       110 PRC           $236.00
1018       304 HRO           $280.00

相关子查询​

相关子查询是引用不在其 FROM 子句中的列或表的子查询。该列可以在 Projection 子句或 WHERE 子句中。

通常，相关子查询会降低性能。建议使用表名或表别名限制子查询中的列名。从而除去与列所驻留的表相关的任何疑问。

下列查询是相关子查询的一个示例，它返回 orders 表中 10 个最近的装运日期的列表。它在子查询之后加上 ORDER BY 子句以对结果进行排序，原因是（除在 FROM 子句以外）您不能在子查询中包括 ORDER BY 。

图: 查询

SELECT po_num, ship_date FROM orders main
WHERE 10 >
(SELECT COUNT (DISTINCT ship_date)
FROM orders sub
WHERE sub.ship_date < main.ship_date)
AND ship_date IS NOT NULL
ORDER BY ship_date, po_num;

因为子查询产生的数取决于 main.ship_date（外部 SELECT 产生的一个值），所以该子查询是相关的。因此，必须对外部查询考虑的每一行重新执行子查询。

该查询使用 COUNT 函数来将值返回到主查询。然后，ORDER BY 子句对数据进行排序。查询找到并返回具有 10 个最新装运日期的 16 行，如下所示。

图: 查询结果

po_num     ship_date

4745       06/21/1998
278701     06/29/1998
429Q       06/29/1998
8052       07/03/1998
B77897     07/03/1998
LZ230      07/06/1998
B77930     07/10/1998
PC6782     07/12/1998
DM354331   07/13/1998
S22942     07/13/1998
MA003      07/16/1998
W2286      07/16/1998
Z55709     07/16/1998
C3288      07/25/1998
KF2961     07/30/1998
W9925      07/30/1998

如果对大型表使用相关子查询（如图 1），那么应对 ship_date 列建立索引以提高性能。否则，此 SELECT 语句效率降低，原因是它对表的每一行执行一次子查询。有关建立索引和性能问题的信息，请参阅《GBase 8s 管理员指南》 和 GBase 8s 性能指南 。

然而，不能在 FROM 子句中使用相关子查询，如下列无效示例所示：

SELECT item_num, stock_num FROM items,
(SELECT stock_num FROM catalog
WHERE stock_num = items.item_num) AS vtab;

该示例中的子查询具有错误 -24138：

ALL COLUMN REFERENCES IN A TABLE EXPRESSION MUST REFER
TO TABLES IN THE FROM CLAUSE OF THE TABLE EXPRESSION.

数据库服务器发出该错误的原因是子查询中的 items.item_num 列还出现在外部查询的 Projection 子句中，但是内部查询的 FROM 子句仅指定catalog 表。错误消息文本中的术语表表达式指的是 FROM 子句中的子查询返回的列值或表达式集合。而在 FROM 子句中，只有不相关子查询才是有效的。

EXISTS 关键字​

关键字 EXISTS 也被称为存在限定符，因为仅当外部 SELECT（如下所示）找到至少一行时，子查询才为 true 。

图: 查询

SELECT UNIQUE manu_name, lead_time
FROM manufact
WHERE EXISTS
(SELECT * FROM stock
WHERE description MATCHES '*shoe*'
AND manufact.manu_code = stock.manu_code);

通常可使用 EXISTS 来构造等价于使用 IN 的查询的查询。下列查询使用 IN 谓词来构造与上述返回相同结果的查询。

图: 查询

SELECT UNIQUE manu_name, lead_time
FROM stock, manufact
WHERE manufact.manu_code IN
(SELECT manu_code FROM stock
WHERE description MATCHES '*shoe*')
AND stock.manu_code = manufact.manu_code;

图 1和图 2返回生产某种鞋的制造商以及预订产品的交付周期的行。该结果显示了返回值。

图: 查询结果

manu_name       lead_time

Anza               5
Hero               4
Karsten           21
Nikolus            8
ProCycle           9
Shimara           30

将关键字 NOT 添加至 IN 或 EXISTS 以创建与前面查询相反的搜索条件。也可以用 !=ALL 代替 NOT IN。

下列查询显示了执行同一操作的两种方法。一种方法可能允许数据库服务器执行相对另一种方法较少的工作，则会取决于数据库的设计和表的大小。要了解哪一种查询更好，使用 SET EXPLAIN 命令来获取查询计划的清单。在 GBase 8s 性能指南 和 GBase 8s SQL 指南：语法 中讨论了 SET EXPLAIN。

图: 查询

SELECT customer_num, company FROM customer
WHERE customer_num NOT IN
(SELECT customer_num FROM orders
WHERE customer.customer_num = orders.customer_num);

SELECT customer_num, company FROM customer
WHERE NOT EXISTS
(SELECT * FROM orders
WHERE customer.customer_num = orders.customer_num);

查询中的每个语句返回下列行，这些行标识尚未下订单的客户。

图: 查询结果

customer_num company

102 Sports Spot
103 Phil's Sports
105 Los Altos Sports
107 Athletic Supplies
108 Quinn's Sports
109 Sport Stuff
113 Sportstown
114 Sporting Place
118 Blue Ribbon Sports
125 Total Fitness Sports
128 Phoenix University

关键字 EXISTS 和 IN 用于称为相交的集合运算，关键 NOT EXISTS 和 NOT IN 用于称为差异的集合运算。这些概念在集合运算中讨论。

下列查询执行对 items 表的子查询来标识 stock 表中尚未预订的所有商品。

图: 查询

SELECT * FROM stock
WHERE NOT EXISTS
(SELECT * FROM items
WHERE stock.stock_num = items.stock_num
AND stock.manu_code = items.manu_code);

该查询返回以下行。

图: 查询结果

stock_num manu_code description unit_price unit unit_descr

101  PRC        bicycle tires       $88.00  box   4/box
102  SHM        bicycle brakes     $220.00  case  4 sets/case
102  PRC        bicycle brakes     $480.00  case  4 sets/case
105  PRC        bicycle wheels      $53.00  pair  pair
⋮
312  HRO        racer goggles       $72.00  box   12/box
313  SHM        swim cap            $72.00  box   12/box
313  ANZ        swim cap            $60.00  box   12/box

对 SELECT 语句可具有的子查询数没有逻辑限制。

您可能想要检查是否在数据库中正确输入了信息。查找数据库中的错误的一种方法是编写仅当错误存在时才会返回输出的查询。这种类型的子查询充当一种审计查询，如下所示。

图: 查询

SELECT * FROM items
WHERE total_price != quantity *
(SELECT unit_price FROM stock
WHERE stock.stock_num = items.stock_num
AND stock.manu_code = items.manu_code);

该查询只返回订单上商品的总价格不等于库存单价乘以订单数量的行。如果没有应用任何折扣，那么可能在数据库中不正确地输入了此类型的行。仅当错误发生时查询才会返回行。如果正确地将信息插入到数据库中，那么不会返回任何行。

图: 查询结果

item_num order_num stock_num manu_code quantity total_price

1      1004         1 HRO              1     $960.00
2      1006         5 NRG              5     $190.00

DELETE 和 UPDATE 语句中的子查询​

除了在 SELECT 的 WHERE 子句中的子查询，还可以在其它数据操纵语言（DML）语句中使用子查询，包括 DELETE 和 UPDATE 的 WHERE 子句。

适用某些限制。如果子查询的 FROM 子句返回多行，并且该子句指定与其它 DML 语句正在修改相同的表或视图，那么处于下列情况下的 DML 语句会成功。

  • DML 语句不能是 INSERT 语句。
  • 子查询中的 SPL 例程没有引用正在被修改的表。
  • 子查询不包括相关列名。
  • 该子查询使用 DELETE 和 UPDATE 的 WHERE 子句中的子查询语法的条件指定。

如果这些条件中的任何条件都不符合，那么 DML 操作发生错误 -360。

以下示例修改 stock 表，通过增加价格子集的 10 % 来增加 unit_price 值。WHERE 子句通过将 IN 运算符应用到从 stock 表查找到 unit_price 值少于 75 而返回的行来增加价格。

UPDATE stock SET unit_price = unit_price * 1.1
WHERE unit_price IN
(SELECT unit_price FROM stock WHERE unit_price < 75);

处理 SELECT 语句中的集合​

数据库服务器提供了下列 SQL 功能来处理集合表达式：

集合子查询

集合子查询采用虚拟表（子查询的结果）并将它转换为集合。

集合子查询总是返回类型 MULTISET 的集合。可使用集合子查询将关系数据库的查询结果转换为 MULTISET 集合。

集合派生的表

集合派生的表采用集合并将它转换为虚拟表。

将集合的每个元素构造成集合派生的表中的行。可以使用集合派生的表来访问集合的个别元素。

集合子查询和集合派生的表功能表示逆操作：集合子查询将关系表的行为转换为集合，而集合派生的表将集合的元素转换为关系表的行。

集合子查询​

集合子查询使用户能够从子查询表达式构造集合表达式。集合子查询在紧邻子查询之前使用 MULTISET 关键字以将返回的值转换为 MULTISET 集合。但是，当在子查询表达式之前使用 MULTISET 关键字时，数据库服务器不会更改基础表的各行而只会修改这些行的副本。例如，如果将集合子查询传递至修改集合的用户定义的例程，那么会修改集合的副本而不会修改基础表。

集合子查询是可采用下列任何形式的表达式：

  • MULTISET(SELECT expression1, expression2... FROM tab_name...)
  • MULTISET(SELECT ITEM expression FROM tab_name...)

在集合子查询中省略 ITEM 关键字​

如果在集合子查询表达式中省略 ITEM 关键字，那么集合子查询就是其元素类型始终为未命名的 ROW 类型的 MULTISET。未命名 ROW 类型的字段与在子查询的 Projection 子句中指定的表达式的数据类型相匹配。

假设您创建了包含类型为 MULTISET 的列的以下表：

CREATE TABLE tab2
(
id_num INT,
ms_col MULTISET(ROW(a INT) NOT NULL)
);

下列查询显示如何在 WHERE 子句中使用集合子查询来将子查询返回的 INT 值的行转换为类型为 MULTISET 的集合。在此示例中，数据库服务器在tab2 的 ms_col 列等于集合子查询表达式的结果时返回行。

图: 查询

SELECT id_num FROM tab2
WHERE ms_col = (MULTISET(SELECT int_col FROM tab1));

该查询在集合子查询中省略了 ITEM 关键字，因此子查询返回的 INT 值类型为 MULTISET（ROW(a INT) NOT NULL）（它与 tab2 的 ms_col 列的数据类型相匹配）。

在集合子查询中指定 ITEM 关键字​

当子查询的投影列表包含单个表达式时， 可以用 ITEM 关键字作为子查询的投影列表的开始以指定 MULTISET 的元素类型与子查询结果的数据类型相匹配。换言之，当包括 ITEM 关键字时，数据库服务器不在投影列表两端放置行包装器。例如：如果子查询（紧跟在 MULTISET 关键字之后）返回 INT 值，集合子查询具有类型 MULTISET（INT NOT NULL）。

假设您创建接受类型为 MULTISET（INT NOT NULL）的参数的函数 int_func()。下列查询显示将具有 INT 值的行转换为 MULTISET 并将集合子查询用作函数 int_func() 中的参数的集合子查询。

图: 查询

EXECUTE FUNCTION int_func(MULTISET(SELECT ITEM int_col
FROM tab1
WHERE int_col BETWEEN 1 AND 10));

该查询在子查询中包括 ITEM 关键字，因此将查询返回的 int_col 值转换为类型为 MULTISET（INT NOT NULL）的集合。没有 ITEM 关键字，集合子查询将返回类型为 MULTISET（ROW(a INT) NOT NULL）的集合。

FROM 子句的集合子查询​

集合子查询在 SELECT 的 FROM 子句中有效，外部查询可使用子查询返回的值作为数据源。

集合子查询这一节中的查询示例通过使用 TABLE 关键字后面（括号内）跟 MULTISET 关键字然后跟子查询来指定集合子查询。该语法是对 SQL 语言的 ANSI/ISO 标准的 GBase 8s 扩展。

在（且仅在）SELECT 语句的 FROM 子句中，可以通过指定子查询、省略 TABLE 和 MULTISET 关键字和嵌套的括号来代替 SQL 的 ANSI/ISO 标准的语法，以指定集合子查询。

下列查询使用 GBase 8s 扩展语法连接外部查询的 FROM 子句中的两个集合子查询：

图: 查询

SELECT * FROM TABLE(MULTISET(SELECT SUM(C1) FROM T1 GROUP BY C1)),
TABLE(MULTISET(SELECT SUM(C1) FROM T2 GROUP BY C2));

通过使用符合 ANSI/ISO 的语法来连接外部查询的 FROM 子句中的两个派生表，下列查询在逻辑上等价于上述返回相同结果的查询：

图: 查询

SELECT * FROM (SELECT SUM(C1) FROM T1 GROUP BY C1),
(SELECT SUM(C1) FROM T2 GROUP BY C2);

该查询优于 TABLE(MULTISET(SELECT ...)) GBase 8s 扩展版本之处在于，任何支持 FROM 子句中符合 ANSI/ISO 语法的数据库服务器也可以执行该查询。有关集合子查询的语法和限制的更多信息，请参阅《GBase 8s SQL 指南：语法》。

集合派生的表​

集合派生的表使您能都处理集合表达式的元素（例如虚拟表中的行）。在 SELECT 语句的 FROM 子句中使用 TABLE 关键字来创建集合派生的表。数据库服务器支持 SELECT 、INSERT 、UPDATE 和 DELETE 语句中的集合派生的表。

以下查询使用名为 c_table 的集合派生表访问 superstores_demo 数据库中 sales_rep 表的 sales 列的元素。sales 列是其中两个字段 month 和amount 存储销售数据的未命名行类型的集合。当 sales.month 等于 98-03 时，下列查询返回 sales.amount 的元素。由于内部选择本身就是表达式，所以它不能对外部查询的每个迭代返回多个列值。外部查询指定对 sales_rep 表的多少行进行求值。

图: 查询

SELECT (SELECT c_table.amount FROM TABLE (sales_rep.sales) c_table
WHERE c_table.month = '98-03')
FROM sales_rep;

图: 查询结果

(expression)

$47.22
$53.22

下列查询使用集合派生的表访问 sales 集合列中 rep_num 列等于 102 的元素。使用集合派生的表，可以为表和列指定列名。如果没有为集合派生的表指定表名，那么数据库服务器会自动创建表名。此示例为集合派生的表 c_table 指定派生列列表 s_month 和 s_amount。

图: 查询

SELECT * FROM TABLE((SELECT sales FROM sales_rep
WHERE sales_rep.rep_num = 102)) c_table(s_month, s_amount);

图: 查询结果

s_month         s_amount

1998-03           $53.22
1998-04           $18.22

下列查询创建集合派生的表但不指定派生表或派生列名。除派生列采用 sales_rep 表中的 sales 列的缺省自动名之外，该查询返回与图 3相同的结果。

图: 查询

SELECT * FROM TABLE((SELECT sales FROM sales_rep
WHERE sales_rep.rep_num = 102));

图: 查询结果

month amount

1998-03 $53.22
1998-04 $18.22
限制

集合派生的表是只读的，因此它不能是 INSERT 、UPDATE 或 DELETE 语句的目标表或可更新游标或视图的基础表。

有关集合派生的表的语法和限制的完整描述，请参阅《GBase 8s SQL 指南：语法》。

用于集合派生表的符合 ISO 的语法​

集合派生的表这一节中的查询示例通过使用 TABLE 关键字后面（括号内）跟 SELECT 语句来指定集合派生的表。该语法是对 SQL 语言的 ANSI/ISO 标准的 GBase 8s 扩展。

但是，在（且仅在）SELECT 的 FROM 子句中，可以通过指定子查询来代替使用 SQL 符合 ANSI/ISO 标准的语法，在不使用 TABLE 关键字或嵌套括号的情况下，来定义集合派生的表。

下列查询在逻辑上等价于 图 3，并为集合派生的表 c_table 指定拍摄了列表 s_month 和 s_amount。

图: 查询

SELECT * FROM (SELECT sales FROM sales_rep
WHERE sales_rep.rep_num = 102) c_table(s_month, s_amount);

图: 查询结果

s_month s_amount

1998-03 $53.22
1998-04 $18.22

如 GBase 8s 扩展语法中一样，声明派生表或其列的名称是可选的，而不是必需的。下列查询对外部查询的 FROM 子句使用符合ANSI/ISO 标准的语法，并产生与图 5相同的结果：

图: 查询

SELECT * FROM (SELECT sales FROM sales_rep
WHERE sales_rep.rep_num = 102);

图: 查询结果

month amount

1998-03 $53.22
1998-04 $18.22

集合运算​

标准集合运算联合、相交和差异允许您处理数据库信息。这三种运算允许您使用 SELECT 语句在执行更新、插入和删除之后检查数据库的完整性。例如：当数据库传送值历史记录表，并且想要从原始表中删除数据之前验证历史记录表中的数据是否正确时，它们就非常有用。

联合​

联合运算使用 UNION 运算符将两个查询组合成单个复合查询。可以在两个或多个 SELECT 语句之间使用 UNION 运算符来产生一个临时表，它包含存在于任何一个原始表或所有原始表中的行。还可以在视图的定义中使用 UNION 运算符。

不能在下列上下文的子查询内使用 UNION 运算符

  • 在 SELECT 语句的 Projection 子句中
  • 在 SELECT 、INSERT 、DELETE 或 UPDATE 语句的 WHERE 子句中

然而，UNION 运算符在 SELECT 语句的 FROM 子句中的子查询中是有效的，如下所示：

SELECT * FROM (SELECT col1 FROM tab1 WHERE col1 = 100) AS vtab1(c1),
(SELECT col1 FROM tab2 WHERE col1 = 10
UNION ALL
SELECT col1 FROM tab1 WHERE col1 < 50 ) AS vtab2(vc1);

GBase 8s 不支持对 ROW 类型进行排序。由于 UNION 操作需要排序以除去重复值，所以当联合运算中的任一查询包括 ROW 类型数据时，不能使用 UNION 操作符。但是，数据库服务器确实支持具有 ROW 类型数据的 UNION ALL，这是因为此类运算无需排序。

下图举例说明了 UNION 集合运算。

图: 联合集合运算

UNION 关键字选择两个查询中的所有行，除去重复行并返回余下的行。因为查询的结果组合为一个结果，所以每个查询中的投影列表必须具有相同的列数。同时，从每个表选择的相应列必须包含兼容的数据类型（CHARACTER 数据类型列的长度必须相同），并且这些相应的列必须全部允许或全部不允许 NULL 值。

有关 SELECT 语句和 UNION 运算符的完整语法，请参阅《GBase 8s SQL 指南：语法》。有关特定于 GBase 8s ESQL/C 产品和涉及 INTO 子句和复合查询的任何限制的信息，请参阅《GBase 8s ESQL/C 程序员手册》。

下列查询对 stock 和 items 表中的 stock_num 和 manu_code 列执行联合。

图: 查询

SELECT DISTINCT stock_num, manu_code FROM stock
WHERE unit_price < 25.00
UNION
SELECT stock_num, manu_code FROM items
WHERE quantity > 3;

该查询选择单价小于 $25.00 或预订数量大于三的那些商品并列出其 stock_num 和 manu_code，如下所示。

图: 查询结果

stock_num manu_code

5 ANZ
5 NRG
5 SMT
9 ANZ
103 PRC
106 PRC
201 NKL
301 KAR
302 HRO
302 KAR

将 ORDER BY 子句与 UNION 一起使用​

如下列查询所示，当包括 ORDER BY 子句时，该子句必须跟在最后的 SELECT 语句后面，并使用整数（而不是标识）来引用排序的列。排序在集合运算完成之后发生。

图: 查询

SELECT DISTINCT stock_num, manu_code FROM stock
WHERE unit_price < 25.00
UNION
SELECT stock_num, manu_code FROM items
WHERE quantity > 3
ORDER BY 2;

之前的复合查询选择与图 2相同的行但以制造商代码的顺序显示它们，如下所示。

图: 查询结果

stock_num manu_code

5 ANZ
9 ANZ
302 HRO
301 KAR
302 KAR
201 NKL
5 NRG
103 PRC
106 PRC
5 SMT

UNION ALL 关键字​

缺省情况下，UNION 关键字排除重复的行。要保留重复值，添加可选关键字 ALL，如下所示。

图: 查询

SELECT stock_num, manu_code FROM stock
WHERE unit_price < 25.00
UNION ALL
SELECT stock_num, manu_code FROM items
WHERE quantity > 3
ORDER BY 2
INTO TEMP stock item;

该查询使用 UNION ALL 关键字联合两个 SELECT 语句并在最后一个 SELECT 后面添加 INTO TEMP 子句来将结果放置到临时列表中。它返回与图 1相同的行，但还包括重复的值。

图: 查询结果

stock_num manu_code

9 ANZ
5 ANZ
9 ANZ
5 ANZ
9 ANZ
⋮
5 NRG
5 NRG
103 PRC
106 PRC
5 SMT
5 SMT

使用不同的列名​

组合查询的 Projection 子句中相应的列必须具有兼容的数据类型，但各列不需要使用相同的列名。

下列查询从 customer 表中选择 state 列及 state 表中的相应 code 列。

图: 查询

SELECT DISTINCT state FROM customer
WHERE customer_num BETWEEN 120 AND 125
UNION
SELECT DISTINCT code FROM state
WHERE sname MATCHES '*a';

该查询返回客户号 120 至 125 的州代码缩写以及其 sname 以 a 结束的州的州代码缩写。

图: 查询结果

state

AK
AL
AZ
CA
DE
⋮
SD
VA
WV

在复合查询中，第一个 SELECT 语句中的列名或显示标注就是出现在结果中的列名或显示标注。因此，在此查询中，使用第一个 SELECT 语句中的列名 state 而不是第一个语句中的列名 code。

将 UNION 与多个表配合使用​

以下查询对三个表执行联合运算。最大联合数取决于应用程序的实用性和任何内存限制。

图: 查询

SELECT stock_num, manu_code FROM stock
WHERE unit_price > 600.00
UNION ALL
SELECT stock_num, manu_code FROM catalog
WHERE catalog_num = 10025
UNION ALL
SELECT stock_num, manu_code FROM items
WHERE quantity = 10
ORDER BY 2;

该查询选择 stock 表中的 unit_price 大于 $600、catalog 表中的 catalog_num 为 10025 或 items 表中的 quantity 为 10 的商品；查询按manu_code 对数据进行排序。该结果显示了返回值。

图: 查询结果

stock_num manu_code

5 ANZ
9 ANZ
8 ANZ
4 HSK
1 HSK
203 NKL
5 NRG
106 PRC
113 SHM

在 Projection 子句中使用文字​

下列查询在投影列表中使用文字来标记联合操作的部分输出，以便今后可以区分它。为该标记提供标号 sortkey。查询使用 sortkey 来对检索到的行进行排序。

图: 查询

SELECT '1' sortkey, lname, fname, company,
city, state, phone
FROM customer x
WHERE state = 'CA'
UNION
SELECT '2' sortkey, lname, fname, company,
city, state, phone
FROM customer y
WHERE state <> 'CA'
INTO TEMP calcust;
SELECT * FROM calcust
ORDER BY 1;

该查询创建一个列表，在该列表中，来自 California 的客户首先显示。

图: 查询结果

sortkey  1
lname    Baxter
fname    Dick
company  Blue Ribbon Sports
city     Oakland
state    CA
phone    415-655-0011

sortkey  1
lname    Beatty
fname    Lana
company  Sportstown
city     Menlo Park
state    CA
phone    415-356-9982
⋮
sortkey  2
lname    Wallack
fname    Jason
company  City Sports
city     Wilmington
state    DE
phone    302-366-7511

使用 FIRST 子句​

可以使用 FIRST 子句来选择由联合查询的产生的前几行。以下查询使用 FIRST 子句返回 stock 和 items 表之间的联合的前五行。

图: 查询

SELECT FIRST 5 DISTINCT stock_num, manu_code
FROM stock
WHERE unit_price < 55.00
UNION
SELECT stock_num, manu_code
FROM items
WHERE quantity > 3;

图: 查询结果

stock_num manu_code

5 NRG
5 ANZ
6 SMT
6 ANZ
9 ANZ

相交​

两个行集的相交产生一个表。它包含同时存在两个原始表的行。使用关键字 EXISTS 或 IN 来引入显示两个集合相交的子查询。下图说明了相交集合运算。

图: 相交集合运算

以下查询是一个嵌套 SELECT 语句的示例，它显示了 stock 和 items 表的交集。该结果包含出现在这两个集合中的所有元素并返回以下行。

图: 查询

SELECT stock_num, manu_code, unit_price FROM stock
WHERE stock_num IN
(SELECT stock_num FROM items)
ORDER BY stock_num;

图: 查询结果

stock_num manu_code unit_price

1 HRO          $250.00
1 HSK          $800.00
1 SMT          $450.00
2 HRO          $126.00
3 HSK          $240.00
3 SHM          $280.00
⋮
306 SHM          $190.00
307 PRC          $250.00
309 HRO           $40.00
309 SHM           $40.00

差异​

两个行集之间的差异产生一个表，它包含在第一个行集中但不在第二个行集中的行。使用关键字 NOT EXISTS 或 NOT IN 俩引入显示两个集合之间的差异的子查询。下图说明了差异集合运算。

图: 差异集合运算

下列查询是嵌套 SELECT 语句的一个示例，它显示了 stock 和 items 表之间的差异。

图: 查询

SELECT stock_num, manu_code, unit_price FROM stock
WHERE stock_num NOT IN
(SELECT stock_num FROM items)
ORDER BY stock_num;

该结果仅包含来自第一个集（它返回了 17 行）的所有元素。

图: 查询结果

stock_num manu_code unit_price

102 PRC          $480.00
102 SHM          $220.00
106 PRC           $23.00
⋮
312 HRO           $72.00
312 SHM           $96.00
313 ANZ           $60.00
313 SHM           $72.00

总结​

本章是基于编写 SELECT 语句中介绍的概念构建的。它提供了更高级类型的 SELECT 语句（用来查询关系数据库）的样本语法和结果。本章提供了以下资料：

  • 介绍了 GROUP BY 和 HAVING 子句，可将这些子句与聚集配合使用来返回行组并对那些组应用条件
  • 显示如何使用自连接来将表连接至它本身以将列中的值与同一列中的其它值进行比较并标识重复
  • 说明外连接如果区别看待两个或多个表，并提供了使用 GBase 8s 扩展和 ANSI 连接语法的四种外连接类型的示例
  • 描述如何在另一 SELECT 语句的 WHERE 子句中嵌套 SELECT 语句，来创建相关和非相关查询并显示如何在子查询中使用聚集函数
  • 演示如何将 SELECT 语句嵌套在另一个 SELECT 语句的 FROM 子句中，来指定其结果作为外部 SELECT 语句数据源的不相关子查询
  • 演示如何使用关键字 ALL 、ANY 、EXISTS 、IN 和 SOME 来创建子查询以及添加关键字 NOT 或关系运算符的影响
  • 描述如何使用集合子查询将关系数据转换为 MULTISET 类型的集合，以及如何使用集合派生的表反访问集合中的元素
  • 讨论联合、相交和差异集合运算
  • 显示任何使用 UNION 和 UNION ALL 关键字创建包含两个或多个 SELECT 语句的复合查询